﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;

namespace Customs.Components.SLXNK
{
	/// <summary>
	/// Tờ khai mậu dịch.
	/// </summary>
	public class ToKhaiMauDich
	{
		public static DataSet Search(int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy, DateTime tuNgay, DateTime denNgay, string maDonVi, string nuocXuatKhau, string nuocNhapKhau, string maCuaKhau, decimal tgkb, byte tgkb_Compare)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
			return Search(connectionString, soToKhai, maLoaiHinh, maHaiQuan, namDangKy, tuNgay, denNgay, maDonVi, nuocXuatKhau, nuocNhapKhau, maCuaKhau, tgkb, tgkb_Compare);
		}

        public static DataSet Search(int soToKhai, string maLoaiHinh, string maHaiQuanMoToKhai, DateTime tuNgay, DateTime denNgay)
        {
            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT TK.SoTK AS SoToKhai, ");
            query.Append("TK.Ngay_DK AS NgayDangKy, ");
            query.Append("TK.Ma_LH AS MaLoaiHinh, ");
            query.Append("LH.Ten_LH AS TenLoaiHinh, ");
            query.Append("TK.Ma_HQ AS MaHaiQuan, ");
            query.Append("HQ.Ten_HQ AS TenHaiQuan, ");
            query.Append("TONGTGKB, ");
            query.Append("TONGTGKB / TyGia_USD AS TONGTGKB_USD, ");
            query.Append("SOHANG, ");
            query.Append("ISNULL(RR.LuongHT, 0) AS LuongHT, ");
            query.Append("(CONVERT(varchar(10), TK.SoTK) + '-' + TK.Ma_LH + '-' + RTRIM(TK.MA_HQ) + '-' + CONVERT(varchar(10), TK.NamDK)) AS Khoa ");
            query.Append("FROM ");
            query.Append("DToKhaiMD TK ");
            query.Append("INNER JOIN SLHINHMD LH ON TK.MA_LH = LH.MA_LH ");
            query.Append("INNER JOIN SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ ");
            query.Append("LEFT OUTER JOIN RR_GHINHANKQ RR ON TK.SOTK = RR.SOTK AND TK.MA_LH = RR.MA_LH AND TK.MA_HQ = RR.MA_HQ AND TK.NAMDK = RR.NAMDK ");

            query.Append("WHERE ");
            if (soToKhai > 0)
                query.Append(string.Format("TK.SoTK = {0} AND ", soToKhai));

            // Loai hinh.
            if (maLoaiHinh != "   ")
                query.Append(string.Format("TK.Ma_LH LIKE '{0}' AND ", maLoaiHinh.PadRight(5)));

            // Hai quan mo to khai.
            if (maHaiQuanMoToKhai != "   ")
                query.Append(string.Format("TK.Ma_HQ LIKE '{0}' AND ", maHaiQuanMoToKhai.PadRight(6)));

            // Tu ngay -> den ngay.
            if (tuNgay.Year > 1900 && denNgay.Year > 1900)
                query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) ");


            query.Append("ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@TuNgay", SqlDbType.DateTime);
            parameters[0].Value = tuNgay;

            parameters[1] = new SqlParameter("@DenNgay", SqlDbType.DateTime);
            parameters[1].Value = denNgay;

            try
            {
                string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuanMoToKhai);
                DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    row["TenLoaiHinh"] = FontConverter.TCVN2Unicode(row["TenLoaiHinh"].ToString());
                    row["TenHaiQuan"] = FontConverter.TCVN2Unicode(row["TenHaiQuan"].ToString());
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + ": " + query);
            }
        }


		public static DataSet Search(string connectionString, int soToKhai, string maLoaiHinh, string maHaiQuanMoToKhai, int namDangKy, DateTime tuNgay, DateTime denNgay, string maDonVi, string nuocXuatKhau, string nuocNhapKhau, string maCuaKhau, decimal tgkb, byte tgkb_Compare)
		{
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT TK.SoTK AS SoToKhai, ");
			query.Append("TK.Ngay_DK AS NgayDangKy, ");
			query.Append("TK.Ma_LH AS MaLoaiHinh, ");
			query.Append("LH.Ten_LH AS TenLoaiHinh, ");
			query.Append("TK.Ma_HQ AS MaHaiQuan, ");
			query.Append("HQ.Ten_HQ AS TenHaiQuan, ");
			query.Append("TONGTGKB, ");
			query.Append("TONGTGKB / TyGia_USD AS TONGTGKB_USD, ");
			query.Append("SOHANG, ");
			query.Append("ISNULL(RR.LuongHT, 0) AS LuongHT, ");
			//query.Append("KQPT.ID, ");
			//query.Append("KQPT.ViPham, ");
			query.Append("(CONVERT(varchar(10), TK.SoTK) + '-' + TK.Ma_LH + '-' + RTRIM(TK.MA_HQ) + '-' + CONVERT(varchar(10), TK.NamDK)) AS Khoa ");
			query.Append("FROM ");
			query.Append("DToKhaiMD TK ");
			query.Append("INNER JOIN SLHINHMD LH ON TK.MA_LH = LH.MA_LH ");
			query.Append("INNER JOIN SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ ");
			query.Append("LEFT OUTER JOIN RR_GHINHANKQ RR ON TK.SOTK = RR.SOTK AND TK.MA_LH = RR.MA_LH AND TK.MA_HQ = RR.MA_HQ AND TK.NAMDK = RR.NAMDK ");
			//query.Append("LEFT OUTER JOIN [RAINBOW_SERVER].IntranetRAINBOW.dbo.t_KTSTQ_KetQuaPhucTap KQPT ON TK.SOTK = KQPT.SoToKhai AND TK.MA_LH = KQPT.MaLoaiHinh AND TK.MA_HQ = KQPT.MaHaiQuan AND TK.NAMDK = YEAR(KQPT.NgayMoToKhai) AND KQPT.IsActive = 1 ");

			query.Append("WHERE ");
			if (soToKhai > 0)
				query.Append(string.Format("TK.SoTK = {0} AND ", soToKhai));

			// Loai hinh.
			if (maLoaiHinh != "   ")
				query.Append(string.Format("TK.Ma_LH LIKE '{0}' AND ", maLoaiHinh));

			// Hai quan mo to khai.
			if (maHaiQuanMoToKhai != "   ")
				query.Append(string.Format("TK.Ma_HQ LIKE '{0}' AND ", maHaiQuanMoToKhai));

			// Nam dang ky.
			if (namDangKy > 1900)
				query.Append(string.Format("TK.NamDK = '{0}' AND ", namDangKy));

			// Tu ngay -> den ngay.
			if (tuNgay.Year > 1900 && denNgay.Year > 1900)
				query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) AND ");

			// Ma don vi.
			if (maDonVi.Length > 0)
				query.Append(string.Format("TK.Ma_DV LIKE '{0}' AND ", maDonVi));

			// Tri gia khai bao.
			switch (tgkb_Compare)
			{
				case 0:
					query.Append("TongTGKB >= @TGKB AND ");
					break;
				case 1:
					query.Append("TongTGKB <= @TGKB AND ");
					break;
				case 2:
					query.Append("TongTGKB = @TGKB AND ");
					break;
			}

			// Nuoc xuat khau.
			if (nuocXuatKhau != "   ")
				query.Append(string.Format("RTRIM(TK.Nuoc_XK) = '{0}' AND ", nuocXuatKhau));

			// Nuoc nhap khau.
			if (nuocNhapKhau != "   ")
				query.Append(string.Format("RTRIM(TK.Nuoc_NK) = '{0}' AND ", nuocNhapKhau));

			// Cua khau xuat, nhap.
			if (maCuaKhau != "   ")
				query.Append(string.Format("TK.Ma_CK LIKE '{0}'", maCuaKhau));
			else
				query.Append("TK.Ma_CK LIKE '%'");

			query.Append(" ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ");

			SqlParameter[] parameters = new SqlParameter[3];
			parameters[0] = new SqlParameter("@TuNgay", SqlDbType.DateTime);
			parameters[0].Value = tuNgay;

			parameters[1] = new SqlParameter("@DenNgay", SqlDbType.DateTime);
			parameters[1].Value = denNgay;

			parameters[2] = new SqlParameter("@TGKB", SqlDbType.Decimal);
			parameters[2].Value = tgkb;

			try
			{
				DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
				foreach (DataRow row in ds.Tables[0].Rows)
				{
					row["TenLoaiHinh"] = FontConverter.TCVN2Unicode(row["TenLoaiHinh"].ToString());
					row["TenHaiQuan"] = FontConverter.TCVN2Unicode(row["TenHaiQuan"].ToString());
				}
				return ds;
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message + ": " + query);
			}
		}

		//-----------------------------------------------------------------------------------------
		public static SqlDataReader SearchByKey(int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
			return SearchByKey(connectionString, soToKhai, maLoaiHinh, maHaiQuan, namDangKy);
		}

        public static SqlDataReader SearchByKey(string key)
        {
            string[] khoa = key.Split('-');
            int soTK = Convert.ToInt32(khoa[0]);
            string maLH = khoa[1];
            string maHQ = khoa[2];
            int namDK = Convert.ToInt32(khoa[3]);

            string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHQ);
            return SearchByKey(connectionString, soTK, maLH, maHQ, namDK);
        }

		//-----------------------------------------------------------------------------------------
		public static DataSet DSSearchByKey(int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
			return DSSearchByKey(connectionString, soToKhai, maLoaiHinh, maHaiQuan, namDangKy);
		}

		//-----------------------------------------------------------------------------------------	
		public static DataSet DSSearchByKey(string connectionString, int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
		{
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT TK.*, ");
			query.Append("DV.Ten_DV, ");
			query.Append("LH.Ten_LH, ");
			query.Append("HQ.Ten_HQ, ");
			query.Append("PTVT.Ten_PTVT AS [TenPTVT], ");
			query.Append("CK.Ten_CK, ");
			query.Append("NXK.Ten_NUOC AS [NuocXK], ");
			query.Append("NNK.Ten_Nuoc AS [NuocNK] ");
			query.Append("FROM ");
			query.Append("dbo.DToKhaiMD TK INNER JOIN ");
			query.Append("dbo.SLHINHMD LH ON TK.MA_LH = LH.MA_LH INNER JOIN ");
			query.Append("dbo.SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ INNER JOIN ");
			query.Append("dbo.SDONVI DV ON TK.Ma_DV = DV.Ma_DV INNER JOIN ");
			query.Append("dbo.SCUAKHAU CK ON TK.MA_CK = CK.Ma_CK INNER JOIN ");
			query.Append("dbo.SPTVT PTVT ON TK.Ma_PTVT = PTVT.Ma_PTVT INNER JOIN ");
			query.Append("dbo.SNUOC NXK ON TK.NUOC_XK = NXK.Ma_Nuoc INNER JOIN ");
			query.Append("dbo.SNUOC NNK ON TK.Nuoc_NK = NNK.Ma_Nuoc ");

			query.Append("WHERE ");
			query.Append("TK.SoTK = @SoToKhai AND ");
			query.Append("TK.Ma_LH = @MaLoaiHinh AND ");
			query.Append("TK.Ma_HQ = @MaHaiQuan AND ");
			query.Append("TK.NamDK = @NamDangKy");

			SqlParameter[] parameters = new SqlParameter[4];
			parameters[0] = new SqlParameter("@SoToKhai", SqlDbType.Int);
			parameters[0].Value = soToKhai;

			parameters[1] = new SqlParameter("@MaLoaiHinh", SqlDbType.Char, 5);
			parameters[1].Value = maLoaiHinh;

			parameters[2] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
			parameters[2].Value = maHaiQuan;

			parameters[3] = new SqlParameter("@NamDangKy", SqlDbType.Int);
			parameters[3].Value = namDangKy;

			return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
		}


		//-----------------------------------------------------------------------------------------	
		public static SqlDataReader SearchByKey(string connectionString, int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
		{
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT TK.*, ");
			query.Append("DV.Ten_DV, ");
			query.Append("LH.Ten_LH, ");
			query.Append("HQ.Ten_HQ, ");
			query.Append("PTVT.Ten_PTVT AS [TenPTVT], ");
			query.Append("CK.Ten_CK, ");
			query.Append("NXK.Ten_NUOC AS [NuocXK], ");
			query.Append("NNK.Ten_Nuoc AS [NuocNK], ");
			query.Append("ISNULL(RR.LuongHT, 0) AS LuongHT ");
			
			query.Append("FROM ");
			
			query.Append("dbo.DToKhaiMD TK LEFT OUTER JOIN ");
			query.Append("dbo.SLHINHMD LH ON TK.MA_LH = LH.MA_LH LEFT OUTER JOIN ");
			query.Append("dbo.SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ LEFT OUTER JOIN ");
			query.Append("dbo.SDONVI DV ON TK.Ma_DV = DV.Ma_DV LEFT OUTER JOIN ");
			query.Append("dbo.SCUAKHAU CK ON TK.MA_CK = CK.Ma_CK LEFT OUTER JOIN ");
			query.Append("dbo.SPTVT PTVT ON TK.Ma_PTVT = PTVT.Ma_PTVT LEFT OUTER JOIN ");
			query.Append("dbo.SNUOC NXK ON TK.NUOC_XK = NXK.Ma_Nuoc LEFT OUTER JOIN ");
			query.Append("dbo.SNUOC NNK ON TK.Nuoc_NK = NNK.Ma_Nuoc LEFT OUTER JOIN ");
			query.Append("RR_GHINHANKQ RR ON TK.SOTK = RR.SOTK AND TK.MA_LH = RR.MA_LH AND TK.MA_HQ = RR.MA_HQ AND TK.NAMDK = RR.NAMDK ");


			query.Append("WHERE ");
			query.Append("TK.SoTK = @SoToKhai AND ");
			query.Append("TK.Ma_LH = @MaLoaiHinh AND ");
			query.Append("TK.Ma_HQ = @MaHaiQuan AND ");
			query.Append("TK.NamDK = @NamDangKy");

			SqlParameter[] parameters = new SqlParameter[4];
			parameters[0] = new SqlParameter("@SoToKhai", SqlDbType.Int);
			parameters[0].Value = soToKhai;

			parameters[1] = new SqlParameter("@MaLoaiHinh", SqlDbType.Char, 5);
			parameters[1].Value = maLoaiHinh;

			parameters[2] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
			parameters[2].Value = maHaiQuan;

			parameters[3] = new SqlParameter("@NamDangKy", SqlDbType.Int);
			parameters[3].Value = namDangKy;

			return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
		}
	}
}